﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class HrmJobSiteDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HrmJobSiteDAL()
        {

        }

        public IList<HrmJobSiteInfo> GetJobSite(string search_text)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<HrmJobSiteInfo> infoList = new List<HrmJobSiteInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@search_text", search_text);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + " SELECT js.JOB_SITE_SEQ"
                            + ", js.JOB_SITE_NAME"
                            + ", ms.SCOPE_NAME"
                            + ", js.RECORD_STATUS"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')"
                        + ") main"
                        + " WHERE RECORD_STATUS <> @delete_status"
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (JOB_SITE_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " or SCOPE_NAME LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        HrmJobSiteInfo info = new HrmJobSiteInfo();
                        SysScopeInfo infoScope = new SysScopeInfo();

                        info.Seq = Convert.ToInt32(dr[i]["JOB_SITE_SEQ"]);
                        info.Name = dr[i]["JOB_SITE_NAME"].ToString();
                        infoScope.Name = dr[i]["SCOPE_NAME"].ToString();

                        info.InfoScope = infoScope;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
    }
}
